<!DOCTYPE html>



  


<html class="theme-next gemini use-motion" lang="zh-Hans">
<head><meta name="generator" content="Hexo 3.8.0">
  <meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, 
maximum-scale=1">
<script src="//cdn.bootcss.com/pace/1.0.2/pace.min.js"></script>
<meta name="theme-color" content="#222">

<script>
    (function () {
        if ('') {
            if (prompt('请输入文章密码') !== '') {
                alert('密码错误！');
                if (history.length === 1) {
                    location.replace("http://xxxxxxx.xxx"); // 这里替换成你的首页
                } else {
                    history.back();
                }
            }
        }
    })();
</script>


  
  
    
    
  <script src="/lib/pace/pace.min.js?v=1.0.2"></script>
  <link href="/lib/pace/pace-theme-minimal.min.css?v=1.0.2" rel="stylesheet">







<meta http-equiv="Cache-Control" content="no-transform">
<meta http-equiv="Cache-Control" content="no-siteapp">
















  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css">







<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css">

<link href="/css/main.css?v=5.1.4" rel="stylesheet" type="text/css">


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png?v=5.1.4">


  <link rel="mask-icon" href="/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="mysql,">










<meta name="description" content="高性能MySQL读书笔记（三）  语法 explain对应的输出解释 相关资料   语法explain &amp;lt;. table_name .&amp;gt; 例如:  1explain select * from table where id=…;">
<meta name="keywords" content="mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="高性能MySQL读书笔记（三）">
<meta property="og:url" content="http://sabistian.cn/2019/11/17/高性能MySQL读书笔记(三)/index.html">
<meta property="og:site_name" content="Sabistian    |      Dear">
<meta property="og:description" content="高性能MySQL读书笔记（三）  语法 explain对应的输出解释 相关资料   语法explain &amp;lt;. table_name .&amp;gt; 例如:  1explain select * from table where id=…;">
<meta property="og:locale" content="zh-Hans">
<meta property="og:updated_time" content="2020-03-03T01:37:23.253Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="高性能MySQL读书笔记（三）">
<meta name="twitter:description" content="高性能MySQL读书笔记（三）  语法 explain对应的输出解释 相关资料   语法explain &amp;lt;. table_name .&amp;gt; 例如:  1explain select * from table where id=…;">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Gemini',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="http://sabistian.cn/2019/11/17/高性能MySQL读书笔记(三)/">





  <title>高性能MySQL读书笔记（三） | Sabistian    |      Dear</title>
  








</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Sabistian    |      Dear</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle"></p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br>
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br>
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br>
            
            标签
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br>
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br>
            
            归档
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="http://sabistian.cn/2019/11/17/高性能MySQL读书笔记(三)/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="邱灵雨">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/images/headicon.png">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Sabistian    |      Dear">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">高性能MySQL读书笔记（三）</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-11-17T22:03:09+08:00">
                2019-11-17
              </time>
            

            

            
          </span>

          
            <span class="post-category">
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/mysql/" itemprop="url" rel="index">
                    <span itemprop="name">mysql</span>
                  </a>
                </span>

                
                
              
            </span>
          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <h1 id="高性能MySQL读书笔记（三）"><a href="#高性能MySQL读书笔记（三）" class="headerlink" title="高性能MySQL读书笔记（三）"></a>高性能MySQL读书笔记（三）</h1><blockquote>
<ul>
<li>语法</li>
<li>explain对应的输出解释</li>
<li>相关资料</li>
</ul>
</blockquote>
<h2 id="语法"><a href="#语法" class="headerlink" title="语法"></a>语法</h2><p>explain &lt;. table_name .&gt;</p>
<p>例如: </p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">explain select * from table where id=…;</span><br></pre></td></tr></table></figure>

<a id="more"></a> 
<h2 id="explain对应的输出解释"><a href="#explain对应的输出解释" class="headerlink" title="explain对应的输出解释"></a>explain对应的输出解释</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+</span><br><span class="line">| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |</span><br><span class="line">+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+</span><br></pre></td></tr></table></figure>

<h3 id="id"><a href="#id" class="headerlink" title="id"></a>id</h3><p>是SQL执行顺利的标志，SQL从大到小的执行，例如：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt; explain select * from (select * from ( select * from user_info where user_id=100000 ;</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |</span><br><span class="line">|  2 | DERIVED     | &lt;derived3&gt; | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |</span><br><span class="line">|  3 | DERIVED     | user_info  | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br></pre></td></tr></table></figure>

<p>很显然这条SQL是从里向外的执行,就是从id=3 向上执行.</p>
<h3 id="select-type"><a href="#select-type" class="headerlink" title="select_type"></a>select_type</h3><p>就是select类型,可以有以下几种</p>
<blockquote>
<ul>
<li>SIMPLE 简单SELECT(不使用UNION或子查询等) 例如:</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt; explain select * from user_info where user_id=100000;</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">|  1 | SIMPLE      | user_info | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>PRIMARY 是最外层的select.例如:</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt; explain select * from (select * from user_info where user_id=100000) a ;</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |</span><br><span class="line">|  2 | DERIVED     | user_info  | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>UNION:UNION中的第二个或后面的SELECT语句.例如</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt;  explain select * from user_info where user_id=100000 union all select * from user_info;</span><br><span class="line">+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+</span><br><span class="line">| id   | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |</span><br><span class="line">+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+</span><br><span class="line">|    1 | PRIMARY      | user_info  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL            |</span><br><span class="line">|    2 | UNION        | user_info  | ALL   | NULL          | NULL    | NULL    | NULL  |    8 | NULL            |</span><br><span class="line">| NULL | UNION RESULT | &lt;union1,2&gt; | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |</span><br><span class="line">+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>DEPENDENT UNION ：UNION中的第二个或后面的SELECT语句，取决于外面的查询</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;explain select * from user_info where user_id in (select user_id from user_info where user_id=100000 union all select user_id from user_info);</span><br><span class="line">+------+--------------------+------------+--------+---------------+---------+---------+-------+------+-----------------+</span><br><span class="line">| id   | select_type        | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra           |</span><br><span class="line">+------+--------------------+------------+--------+---------------+---------+---------+-------+------+-----------------+</span><br><span class="line">|    1 | PRIMARY            | user_info  | ALL    | NULL          | NULL    | NULL    | NULL  |    8 | Using where     |</span><br><span class="line">|    2 | DEPENDENT SUBQUERY | user_info  | const  | PRIMARY       | PRIMARY | 4       | const |    1 | Using index     |</span><br><span class="line">|    3 | DEPENDENT UNION    | user_info  | eq_ref | PRIMARY       | PRIMARY | 4       | func  |    1 | Using index     |</span><br><span class="line">| NULL | UNION RESULT       | &lt;union2,3&gt; | ALL    | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |</span><br><span class="line">+------+--------------------+------------+--------+---------------+---------+---------+-------+------+-----------------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>UNION RESULT ：UNION的结果。</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt; explain select * from user_info where user_id=100000 union all select * from user_info ;</span><br><span class="line">+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+</span><br><span class="line">| id   | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |</span><br><span class="line">+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+</span><br><span class="line">|    1 | PRIMARY      | user_info  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL            |</span><br><span class="line">|    2 | UNION        | user_info  | ALL   | NULL          | NULL    | NULL    | NULL  |    8 | NULL            |</span><br><span class="line">| NULL | UNION RESULT | &lt;union1,2&gt; | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |</span><br><span class="line">+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>SUBQUERY : 子查询中的第一个SELECT.</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt; explain select * from user_info where user_id = (select user_id from user_info where user_id=100000);</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------------+</span><br><span class="line">| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra       |</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------------+</span><br><span class="line">|  1 | PRIMARY     | user_info | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL        |</span><br><span class="line">|  2 | SUBQUERY    | user_info | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>DEPENDENT SUBQUERY : 子查询中的第一个SELECT，取决于外面的查询</li>
<li>DERIVED : 派生表的SELECT(FROM子句的子查询)</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt; explain select * from (select * from user_info where user_id=100000)a;</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |</span><br><span class="line">|  2 | DERIVED     | user_info  | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br></pre></td></tr></table></figure>

<h3 id="table"><a href="#table" class="headerlink" title="table"></a>table</h3><p>显示这一行的数据是关于哪张表的.有时不是真实的表名字,看到的是derivedx(x是个数字, 是第几步执行的结果)。如：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt; explain select * from (select * from ( select * from user_info where user_id=100000) a) b;</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |</span><br><span class="line">|  2 | DERIVED     | &lt;derived3&gt; | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |</span><br><span class="line">|  3 | DERIVED     | user_info  | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br></pre></td></tr></table></figure>

<h3 id="type"><a href="#type" class="headerlink" title="type"></a>type</h3><p>这列很重要,显示了连接使用了哪种类别,有无使用索引.从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL。</p>
<blockquote>
<ul>
<li>system ：这是const联接类型的一个特例。表仅有一行满足条件.如下(t3表上的id是 primary key)</li>
<li>const ：表最多有一个匹配行，它将在查询开始时被读取，因为仅有一行，在这行的列值可被优化器剩余部分认为是常数，而优化处理。<br>const表很快，因为它们只读取一次！const用于用常数值比较PRIMARY,KEY或UNIQUE索引的所有部分时。在下面的查询中，tbl_name可以用于const表：</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">SELECT * from tbl_name WHERE primary_key=1；</span><br><span class="line">SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2；</span><br><span class="line"></span><br><span class="line">mysql.&gt; explain select * from (select * from user_info where user_id=100000)a;</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br><span class="line">|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |</span><br><span class="line">|  2 | DERIVED     | user_info  | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |</span><br><span class="line">+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>eq_ref ：与前表的每个数据关联，只有一条数据被读取出来。除了system、const类型，这是最好的Type。通常，<br>当一个索引的所有部分都在join中使用(完整的索引被使用)并且这个索引的类似是主键索引或者是非空唯一索引时，会产生这种类型。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。<br>在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt;explain select * from t_user_talents,t_user_sign_in where t_user_talents.user_guid=t_user_sign_in.user_guid;</span><br><span class="line">+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------------+------+-------+</span><br><span class="line">| id | select_type | table          | type   | possible_keys | key     | key_len | ref                                      | rows | Extra |</span><br><span class="line">+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------------+------+-------+</span><br><span class="line">|  1 | SIMPLE      | t_user_talents | ALL    | PRIMARY       | NULL    | NULL    | NULL                                     |    1 | NULL  |</span><br><span class="line">|  1 | SIMPLE      | t_user_sign_in | eq_ref | PRIMARY       | PRIMARY | 8       | low_game_schema.t_user_talents.user_guid |    1 | NULL  |</span><br><span class="line">+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------------+------+-------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>ref ：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARYKEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或&lt;.=.&gt;操作符的带索引的列。在下面的例子中，MySQL可以使用ref联接来处理ref_tables：</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt;explain select * from user_info,t_legion_member_info where user_info.user_guid=t_legion_member_info.member_guid;</span><br><span class="line">+----+-------------+----------------------+------+---------------+-----------+---------+--------------------------------------------------+------+-------+</span><br><span class="line">| id | select_type | table                | type | possible_keys | key       | key_len | ref                                              | rows | Extra |</span><br><span class="line">+----+-------------+----------------------+------+---------------+-----------+---------+--------------------------------------------------+------+-------+</span><br><span class="line">|  1 | SIMPLE      | t_legion_member_info | ALL  | PRIMARY       | NULL      | NULL    | NULL                                             |    3 | NULL  |</span><br><span class="line">|  1 | SIMPLE      | user_info            | ref  | user_guid     | user_guid | 8       | low_game_schema.t_legion_member_info.member_guid |    1 | NULL  |</span><br><span class="line">+----+-------------+----------------------+------+---------------+-----------+---------+--------------------------------------------------+------+-------+</span><br></pre></td></tr></table></figure>

<p>二者的区别就在使用的索引是否是唯一索引。</p>
<blockquote>
<ul>
<li>ref_or_null ：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM ref_table</span><br><span class="line">WHERE key_column=expr OR key_column IS NULL;</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>index_merge ：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。例如:<br>笔者用的是5.6版本= =5.7版本以上才有这个功能</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt;EXPLAIN SELECT * FROM order t WHERE t.PrdModel = &apos;NX549J&apos; AND t.Destination = &apos;国内&apos; AND t.OEM = &apos;福盛创新&apos;; </span><br><span class="line">+----+-------------+-------+------------+-------------+--------------------------+--------------+---------+------+------+----------+--------------------------------------------+</span><br><span class="line">| id | select_type | table | partitions | type        | possible_keys            | key          | key_len | ref  | rows | filtered | Extra                                      |</span><br><span class="line">+----+-------------+-------+------------+-------------+--------------------------+--------------+---------+------+------+----------+--------------------------------------------+</span><br><span class="line">|  1 | SIMPLE      | t     | NULL       | index_merge | PrdModel,Destination,OEM | OEM,PrdModel | 153,153 | NULL |  614 |    50.00 | Using intersect(OEM,PrdModel); Using where |</span><br><span class="line">+----+-------------+-------+------------+-------------+--------------------------+--------------+---------+------+------+----------+--------------------------------------------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>unique_subquery ：该类型替换了下面形式的IN子查询的ref<br>value IN (SELECT primary_key FROM single_table WHERE some_expr)<br>(in的后面是一个查询主键字段的子查询)<br>unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。<br>unique 类似主键的唯一索引</li>
</ul>
</blockquote>
<blockquote>
<ul>
<li>index_subquery ：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)</li>
</ul>
</blockquote>
<blockquote>
<ul>
<li>range ：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、&lt;…&gt;、.&gt;、.&gt;=、&lt;.、&lt;.=、IS，NULL、&lt;.=.&gt;、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql.&gt; explain select * from user_info where user_id=100000 or user_id=100001 ;</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+</span><br><span class="line">| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+</span><br><span class="line">|  1 | SIMPLE      | user_info | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |</span><br><span class="line">+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+</span><br></pre></td></tr></table></figure>

<blockquote>
<ul>
<li>index ：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。</li>
<li>ALL ：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。</li>
</ul>
</blockquote>
<h3 id="possible-keys"><a href="#possible-keys" class="headerlink" title="possible_keys"></a>possible_keys</h3><p>possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。</p>
<p>如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询</p>
<h3 id="key"><a href="#key" class="headerlink" title="key"></a>key</h3><p>key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。</p>
<h3 id="key-len"><a href="#key-len" class="headerlink" title="key_len"></a>key_len</h3><p>key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好。</p>
<h3 id="ref"><a href="#ref" class="headerlink" title="ref"></a>ref</h3><p>ref列显示使用哪个列或常数与key一起从表中选择行。</p>
<h3 id="rows"><a href="#rows" class="headerlink" title="rows"></a>rows</h3><p>rows列显示MySQL认为它执行查询时必须检查的行数。当TYPE字段是ref类型，那么rows字段表示每个产生的结果集数量。比如，1代表每次关联查询返回1条记录。</p>
<h3 id="extra"><a href="#extra" class="headerlink" title="extra"></a>extra</h3><p>该列包含MySQL解决查询的详细信息,下面详细.</p>
<ul>
<li>Distinct : 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了。</li>
<li>Not exists : MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行。</li>
<li>Range checked for each : Record（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。</li>
<li>Using filesort : 看到这个的时候，查询就需要优化了。MYSQL需要进行<strong>额外的步骤（无法使用索引的有序性）</strong>来发现如何对返回的行排序，数据量小（小于排序缓冲区）的时候会在内存中快速排序，数据量大的时候会在磁盘中排序:将所有数据分成多个块，每个块做快速排序，将每个块的排序结果放到磁盘上，然后合并输出。</li>
<li>Using index : 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。</li>
<li>Using temporary :看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。</li>
<li>Using where : 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。using where往往暗示mysql需要从数据表中读取记录然后过滤。正好与using index不同。</li>
<li>Using index condition : stackoverflow的解答，简答来说，就是select的字段中一部分在索引里存在，还有一部分不存在，只能先拿了索引里的，然后回表读取响应的数据。</li>
</ul>
<h2 id="相关资料"><a href="#相关资料" class="headerlink" title="相关资料"></a>相关资料</h2><p><a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html" target="_blank" rel="noopener">参考资料</a></p>

      
    </div>
    
    
    

    

    
      <div>
        <div style="padding: 10px 0; margin: 20px auto; width: 90%; text-align: center;">
  <div></div>
  <button id="rewardButton" disable="enable" onclick="var qr = document.getElementById('QR'); if (qr.style.display === 'none') {qr.style.display='block';} else {qr.style.display='none'}">
    <span>打赏</span>
  </button>
  <div id="QR" style="display: none;">

    
      <div id="wechat" style="display: inline-block">
        <img id="wechat_qr" src="/images/wechatpay.jpg" alt="邱灵雨 微信支付">
        <p>微信支付</p>
      </div>
    

    
      <div id="alipay" style="display: inline-block">
        <img id="alipay_qr" src="/images/alipay.jpg" alt="邱灵雨 支付宝">
        <p>支付宝</p>
      </div>
    

    

  </div>
</div>

      </div>
    

    
      <div>
        
      <ul class="post-copyright">
         <li class="post-copyright-link">
          <strong>本文作者：</strong>
          <a href="/" title="欢迎访问 邱灵雨 的个人博客">邱灵雨</a>
        </li>

        <li class="post-copyright-link">
          <strong>本文链接：</strong>
          <a href="http://sabistian.cn/2019/11/17/高性能MySQL读书笔记(三)/" title="高性能MySQL读书笔记（三）">http://sabistian.cn/2019/11/17/高性能MySQL读书笔记(三)/</a>
        </li>

        <li class="post-copyright-license">
          <strong>版权声明： </strong>
          本文由 邱灵雨 原创，采用 <a href="https://creativecommons.org/licenses/by-nc-nd/4.0/deed.zh" rel="license" target="_blank">保留署名-非商业性使用-禁止演绎 4.0-国际许可协议</a> <br>转载请保留以上声明信息！
        </li>
      </ul>
<div style="text-align:center;color: #ccc;font-size:14px;">
------ 本文结束 ------</div>
      </div>
    

    <footer class="post-footer">
      
      
        <div class="post-tags">
          
            <a href="/tags/mysql/" rel="tag"><i class="fa fa-tag"></i> mysql</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2019/11/03/高性能MySQL读书笔记(二)/" rel="next" title="高性能MySQL读书笔记（二）">
                <i class="fa fa-chevron-left"></i> 高性能MySQL读书笔记（二）
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2019/11/17/LinuxInstructions-1/" rel="prev" title="Linux常用指令（一）">
                Linux常用指令（一） <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image" src="/images/headicon.png" alt="邱灵雨">
            
              <p class="site-author-name" itemprop="name">邱灵雨</p>
              <p class="site-description motion-element" itemprop="description">灵雨既零</p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/archives/">
              
                  <span class="site-state-item-count">21</span>
                  <span class="site-state-item-name">日志</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-categories">
                <a href="/categories/index.html">
                  <span class="site-state-item-count">7</span>
                  <span class="site-state-item-name">分类</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/tags/index.html">
                  <span class="site-state-item-count">20</span>
                  <span class="site-state-item-name">标签</span>
                </a>
              </div>
            

          </nav>

          

          
            <div class="links-of-author motion-element">
                
                  <span class="links-of-author-item">
                    <a href="https://github.com/sabistian" target="_blank" title="GitHub">
                      
                        <i class="fa fa-fw fa-github"></i>GitHub</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="mailto:sebistian@163.com" target="_blank" title="Mail">
                      
                        <i class="fa fa-fw fa-envelope"></i>Mail</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://weibo.com/5975979965/profile?rightmod=1&wvr=6&mod=personinfo" target="_blank" title="Weibo">
                      
                        <i class="fa fa-fw fa-weibo"></i>Weibo</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://www.zhihu.com/people/kuai-shen-l/activities" target="_blank" title="ZhiHu">
                      
                        <i class="fa fa-fw fa-globe"></i>ZhiHu</a>
                  </span>
                
            </div>
          

          
          

          
          

          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#高性能MySQL读书笔记（三）"><span class="nav-number">1.</span> <span class="nav-text">高性能MySQL读书笔记（三）</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#语法"><span class="nav-number">1.1.</span> <span class="nav-text">语法</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#explain对应的输出解释"><span class="nav-number">1.2.</span> <span class="nav-text">explain对应的输出解释</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#id"><span class="nav-number">1.2.1.</span> <span class="nav-text">id</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#select-type"><span class="nav-number">1.2.2.</span> <span class="nav-text">select_type</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#table"><span class="nav-number">1.2.3.</span> <span class="nav-text">table</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#type"><span class="nav-number">1.2.4.</span> <span class="nav-text">type</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#possible-keys"><span class="nav-number">1.2.5.</span> <span class="nav-text">possible_keys</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#key"><span class="nav-number">1.2.6.</span> <span class="nav-text">key</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#key-len"><span class="nav-number">1.2.7.</span> <span class="nav-text">key_len</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#ref"><span class="nav-number">1.2.8.</span> <span class="nav-text">ref</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#rows"><span class="nav-number">1.2.9.</span> <span class="nav-text">rows</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#extra"><span class="nav-number">1.2.10.</span> <span class="nav-text">extra</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#相关资料"><span class="nav-number">1.3.</span> <span class="nav-text">相关资料</span></a></li></ol></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

<div class="copyright">&copy; <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">邱灵雨</span>

  
</div>

<div class="powered-by">
<i class="fa fa-user-md"></i><span id="busuanzi_container_site_uv">
  本站访客数:<span id="busuanzi_value_site_uv"></span>
</span>
</div>


  <div class="powered-by">由 <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> 强力驱动</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">主题 &mdash; <a class="theme-link" target="_blank" href="https://github.com/iissnan/hexo-theme-next">NexT.Gemini</a> v5.1.4</div>




        







        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.4"></script>



  
  


  <script type="text/javascript" src="/js/src/affix.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/schemes/pisces.js?v=5.1.4"></script>



  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  












  





  

  

  

  
  

  

  

  

</body>
</html>
